import requests
from spreadsheet.data import *


def geocode_address(api_key,address):
    url = f"https://maps.googleapis.com/maps/api/geocode/json?key={api_key}&address={address}&components=country:US"
    response = requests.get(url)
    return response.json()

if __name__ == "__main__":
    api_key = 'AIzaSyAl2PL3mZLb_chEZf8tlkWg_hGOUH4J8JQ'
    data = ready('Nz78s1dANh6S19tsW8pcCnRsnde','Sheet17')
    for i in range(len(data)):
        if i > 1:
            address = data[i][9]
            result = geocode_address(api_key, address)
            if result['status'] == 'OK':
                address_result = result['results'][0]['formatted_address']
                if address_result != '美国':
                    print(address_result)
                    data[i][10] = address_result
    over_data('Nz78s1dANh6S19tsW8pcCnRsnde','Sheet17',data)
    print('操作完成')
